package util;

import db.ConnectionManager;
import java.security.MessageDigest;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;

/**
 * This method is for storing, calling, and interpreting specific queries.
 * It uses the ConnectionManager as the underlying MySQL input.
 *
 * It has lots of specialized methods.
 *
 * Last Modified: Aug 24, 2010<br>
 * Version: 1.0<br>
 * Created: Aug 11, 2010<br>
 * Author: Katie Kuksenok (katie.kuksenok@gmail.com)
 *
 * @author Katie
 */
public class CleverBrowser {

    /* Technically, anyone is free to change these table names, but these are used by the suppled .sql script */
    private final String contentsTable = "contents";
    private final String methodsTable = "methods";
    private final String methodContentTable = "methodcontent";
    private final String methodSeedsTable = "methodseeds";

    /* Getters */
    private PreparedStatement getLatestURLContent;
    private PreparedStatement getLastUpdateTime;
    private PreparedStatement getMethodID;
    private PreparedStatement getContentCount;
    private PreparedStatement getContentByID;
    private PreparedStatement getContentByMID;
    private PreparedStatement getMethodCount;
    private PreparedStatement getMethodSeeds;
    private PreparedStatement getCurrentTime;
    /* Adders */
    private PreparedStatement addContent;
    private PreparedStatement addMethod;
    private PreparedStatement addMethodContent;
    private PreparedStatement addMethodSeed;
    /* Connectio nvariables */
    private ConnectionManager qm = null;
    private boolean verbose;

    /**
     * The first four paramters specify where appropriate tables are to be found;
     * setting verbose to true makes for error output (not just returning nulls)
     * @param host
     * @param schema
     * @param user
     * @param password
     * @param verbose
     */
    public CleverBrowser(String host, String schema, String user, String password, boolean verbose) {
        this.verbose = verbose;
        qm = new ConnectionManager(host, schema, user, password);
        if (qm.isOk() && verbose) {
            print("Database connection with " + host + " MySQL established.");
        }

        getLatestURLContent = qm.getPS("SELECT Content FROM " + contentsTable + " WHERE mdurl=? and contentid in (SELECT contentid from " + methodContentTable + " where methodid=?) and url=? order by scraped desc limit 1");
        getContentByID = qm.getPS("SELECT Content FROM " + contentsTable + " WHERE contentid=?");
        getLastUpdateTime = qm.getPS("SELECT Scraped FROM " + contentsTable + " WHERE mdurl=? and contentid in (SELECT contentid from " + methodContentTable + " where methodid=?) and url=? order by scraped desc limit 1");
        getMethodID = qm.getPS("SELECT methodid FROM " + methodsTable + " WHERE methodname=?");
        getContentCount = qm.getPS("SELECT COUNT(*) FROM " + contentsTable);
        getMethodCount = qm.getPS("SELECT COUNT(*) FROM " + methodsTable);
        getMethodSeeds = qm.getPS("SELECT url FROM " + methodSeedsTable + " WHERE methodid=?");
        getCurrentTime = qm.getPS("SELECT NOW()");
        getContentByMID = qm.getPS("SELECT contentid, scraped, Url from contents where contentid in (select contentid from methodcontent where methodid=?) order by url, scraped DESC");
        
        addMethodSeed = qm.getPS("INSERT INTO " + methodSeedsTable + " SET methodid=?, mdurl=?, url=?");
        addMethod = qm.getPS("INSERT INTO " + methodsTable + " SET methodname=?, methodid=?");
        addMethodContent = qm.getPS("INSERT INTO " + methodContentTable + " SET methodid=?, contentid=?");
        addContent = qm.getPS("INSERT INTO " + contentsTable + " SET Content=?, Url=?, Mdurl=?, contentid=?");
    }

    /**
     * Is the connection alive?
     * @return true iff there is a ConnectionManager instance and it itself return true on isOK()
     */
    public boolean isOK() {
        return (qm != null && qm.isOk());
    }

    /**
     * Kills the queryManager.
     * @return true iff calling kill() on ConnectionManager instance returned true.
     */
    public boolean kill() {
        return qm.kill();
    }

    /**
     * Returns an ID for the given method name (no more than one ID
     * may be iven to the same name!). If no such exists, a new method is created,
     * and its ID returned.
     *
     * Please see http://code.google.com/p/nicenet/wiki/SoCalledMethods
     * for an explanation of what a method is supposed ot mean here
     *
     * @param methodName - the name
     * @return - the id; OR -1 if the database is not cooperating.
     */
    public int getMethodID(String methodName) {
        if (!isOK()) {
            print("getMethodID(" + methodName + ") QueryManager was not OK.");
            return -1;
        }
        try {
            getMethodID.setString(1, methodName);
            ResultSet rs = qm.select(getMethodID);
            if (rs.first()) {
                return rs.getInt(1);
            } else {
                ResultSet rs2 = qm.select(getMethodCount);
                rs2.first();

                addMethod.setString(1, methodName);
                addMethod.setInt(2, rs2.getInt(1));
                qm.update(addMethod);
                return getMethodID(methodName);
            }

        } catch (Exception e) {
            print("getMethodID(" + methodName + ") Unable to make query");
        }
        return -1;
    }

    /**
     * Associate seeds with a method.
     * @param URLs
     * @param methodID
     */
    public void setSeeds(ArrayList<String> URLs, int methodID) {
        if (!isOK()) {
            print("setSeeds(.." + URLs.size() + "..," + methodID + ") QueryManager was not OK.");
            return;
        }
        try {
            addMethodSeed.setInt(1, methodID);
            for (String url : URLs) {
                addMethodSeed.setString(2, SimpleMD5.MD5(url));
                addMethodSeed.setString(3, url);
                qm.update(addMethodSeed);
            }
        } catch (Exception e) {
            print("setSeeds(.." + URLs.size() + "..," + methodID + ") Unable to make query");
        }
    }

    /**
     * Get seeds from given method
     * @param methodID
     * @return
     */
    public ArrayList<String> getSeeds(int methodID) {
        if (!isOK()) {
            print("getSeeds(" + methodID + ") QueryManager was not OK.");
            return null;
        }
        try {
            getMethodSeeds.setInt(1, methodID);
            ArrayList<String> out = new ArrayList<String>();
            ResultSet rs = qm.select(getMethodSeeds);
            if (rs.first()) {
                do {
                    out.add(rs.getString(1));
                } while (rs.next());
            }
            return out;
        } catch (Exception e) {
            print("getSeeds(" + methodID + ") Unable to make query");
        }
        return null;
    }

    /**
     * Get results
     * @param methodID
     * @return
     */
    public HashMap<String, ArrayList<ResultRow>> getResults(int methodID) {
        if (!isOK()) {
            print("getContentCount(" + methodID + ") QueryManager was not OK.");
            return null;
        }
        try {
            getContentByMID.setInt(1, methodID);
            ResultSet rs = qm.select(getContentByMID);
            HashMap<String, ArrayList<ResultRow>> out = new HashMap<String, ArrayList<ResultRow>>();
            if (rs.first()) {
                do {
                    String URL = rs.getString(3);
                    Timestamp ts = rs.getTimestamp(2);
                    int id = rs.getInt(1);
                    ArrayList<ResultRow> set = out.get(URL);
                    if (set == null) {
                        set = new ArrayList<ResultRow>();
                        out.put(URL, set);
                    }
                    set.add(new ResultRow(id, ts));
                } while (rs.next());
            }
            return out;
        } catch (Exception e) {
            print("getContentCount(" + methodID + ") Unable to make query");
        }
        return null;
    }

    /**
     * Insert a new entry into contents
     * @param URL
     * @param contents
     * @param methodID
     * @return
     */
    public boolean addContent(String URL, StringBuilder contents, int methodID) {
        if (!isOK()) {
            print("addContent(" + URL + ",...) QueryManager was not OK.");
            return false;
        }
        try {

            ResultSet rs = qm.select(getContentCount);
            if (rs.first()) {
                int contentID = rs.getInt(1);
                String mdurl = SimpleMD5.MD5(URL);
                addContent.setString(1, contents.toString());
                addContent.setString(2, URL);
                addContent.setString(3, mdurl);
                addContent.setInt(4, contentID);
                addMethodContent.setInt(1, methodID);
                addMethodContent.setInt(2, contentID);

                boolean out = qm.update(addContent);
                out = out && qm.update(addMethodContent);
                return out;
            }

        } catch (Exception e) {
            print("addContent(" + URL + ",...) Unable to make query");
        }

        return false;

    }

    /**
     * etrieve the most recently-updates text for this URL
     * @param URL
     * @param methodID
     * @return
     */
    public String getLastUpdate(String URL, int methodID) {
        if (!isOK()) {
            print("getLastUpdate(" + URL + ") QueryManager was not OK.");
            return null;
        }
        try {
            String mdurl = SimpleMD5.MD5(URL);
            getLatestURLContent.setString(1, mdurl);
            getLatestURLContent.setInt(2, methodID);
            getLatestURLContent.setString(3, URL);
            ResultSet rs = qm.select(getLatestURLContent);
            if (rs.first()) {
                return rs.getString(1);
            }
        } catch (Exception e) {
            print("getLastUpdate(" + URL + ") Unable to make query");
        }
        return null;
    }

    /**
     * Retrieves the full text of asingle entry.
     * @param contentID
     * @return
     */
    public String getContentByID(int contentID) {
        if (!isOK()) {
            print("getContentByID(" + contentID + ") QueryManager was not OK.");
            return null;
        }
        try {
            getContentByID.setInt(1, contentID);
            ResultSet rs = qm.select(getContentByID);
            if (rs.first()) {
                return rs.getString(1);
            }
        } catch (Exception e) {
            print("getContentByID(" + contentID + ") Unable to make query");
        }
        return null;
    }

    /**
     * Gets the current time accoridng to the database.
     * Desirable over the system time on the computer running, as those may not correspond
     * (for example different timezones), and the results will be unusable.
     * @return
     */
    public Timestamp getCurrentTime() {
        if (!isOK()) {
            print("getCurrentTime() QueryManager was not OK.");
            return null;
        }
        try {
            ResultSet rs = qm.select(getCurrentTime);
            if (rs.first()) {
                return rs.getTimestamp(1);
            }
        } catch (Exception e) {
            print("getCurrentTime() Unable to make query");
        }
        return null;
    }

    /**
     * The last time a URL has bee updated
     * @param URL
     * @param methodID
     * @return
     */
    public Timestamp getLastUpdateTime(String URL, int methodID) {
        if (!isOK()) {
            print("getLastUpdateTime(" + URL + ") QueryManager was not OK.");
            return null;
        }
        try {
            String mdurl = SimpleMD5.MD5(URL);
            getLastUpdateTime.setString(1, mdurl);
            getLastUpdateTime.setInt(2, methodID);
            getLastUpdateTime.setString(3, URL);
            ResultSet rs = qm.select(getLastUpdateTime);
            if (rs.first()) {
                return rs.getTimestamp(1);
            }
        } catch (Exception e) {
            print("getLastUpdateTime(" + URL + ") Unable to make query");
        }
        return null;
    }

    /**
     * The time, in millis, since the last update until now
     * (now is given by getCurrentTime())
     * @param URL
     * @param methodID
     * @return
     */
    public long getTimeSinceLastUpdate(String URL, int methodID) {
        Timestamp last = getLastUpdateTime(URL, methodID);
        Timestamp current = getCurrentTime();
        if (last == null || current == null) {
            return -1;
        } else {
            return current.getTime() - last.getTime();
        }
    }

    /**
     * Simple boolean function for detemring if something's been updated
     * within a given millis interval
     * @param URL
     * @param methodID
     * @param timeInMillis
     * @return
     */
    public boolean beenUpdatedWithin(String URL, int methodID, long timeInMillis) {
        if (timeInMillis <= 0) {
            return false;
        }
        long diff = getTimeSinceLastUpdate(URL, methodID);
        if (diff != -1 && diff <= timeInMillis) {
            return true;
        }
        return false;
    }

    private void print(String message) {
        if (verbose) {
            System.out.println(message);
        }
    }
}
